package com.navi.mould1.service.impl;

import com.navi.bean.in.ConditionBean;
import com.navi.bean.out.halm_sourceDataExport.HalmSourceDataExportReportDataBean;
import com.navi.bean.sql.SqlVo;
import com.navi.mould1.service.IHalmSourceDataExportReportService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;
import static com.navi.bean.sql.SqlVo.jointInSql;
import static com.navi.core.util.JdbcUtils.mySql_QueryRunner_QueryListBySql;


@Slf4j
@Service
public class HalmSourceDataExportReportServiceImpl implements IHalmSourceDataExportReportService {


    public void generateExcel(ConditionBean conditions, HttpServletResponse response) {
        final SqlVo sqlVo = new SqlVo();
        sqlVo.appendSql("SELECT");
        sqlVo.appendSql("*");
        sqlVo.appendSql("FROM halm.halm_test_data A");
        sqlVo.appendSql("WHERE 1=1");
        if (StringUtils.isNotEmpty(conditions.getFab_id())) sqlVo.appendSql("AND A.TOOL_ID_FK IN(select tool_id from icim_jn.bis_tool where fab_id_fk in" + jointInSql(conditions.getFab_id()) + ")");
        if (StringUtils.isNotEmpty(conditions.getBay_id())) sqlVo.appendSql("AND A.LINE_ID_FK in" + jointInSql(conditions.getBay_id()));
        if (StringUtils.isNotEmpty(conditions.getToolg_id())) sqlVo.appendSql("AND A.TOOL_ID_FK in(select tool_id from icim_jn.bis_tool where root_tool_id in (select tool_id_fk from bis_tool_gr where toolg_id in " + jointInSql(conditions.getToolg_id()) + "))");
        if (StringUtils.isNotEmpty(conditions.getTool_type())) sqlVo.appendSql("AND A.TOOL_ID_FK in(select tool_id from icim_jn.bis_tool where unit_typ in" + jointInSql(conditions.getTool_type()) + ")");
        if (StringUtils.isNotEmpty(conditions.getTool_id())) sqlVo.appendSql("AND A.TOOL_ID_FK in" + jointInSql(conditions.getTool_id()));
        if (StringUtils.isNotEmpty(conditions.getWafer_spec())) sqlVo.appendSql("AND A.WAFER_SPEC in" + jointInSql(conditions.getWafer_spec()));
        if (StringUtils.isNotEmpty(conditions.getManufacturer())) sqlVo.appendSql("AND A.CUS_ID_FK in" + jointInSql(conditions.getManufacturer()));
        if (StringUtils.isNotEmpty(conditions.getBase_id())) sqlVo.appendSql("AND A.BASE_ID in" + jointInSql(conditions.getBase_id()));
        //if (StringUtils.isNotEmpty(conditions.getAttributes())) sqlVo.appendSql("AND A.ATTRIBUTES in" + jointInSql(conditions.getAttributes()));
        if (StringUtils.isNotEmpty(conditions.getLot_id())) sqlVo.appendSql("AND A.LOT_ID in" + jointInSql(conditions.getLot_id()));
        if (StringUtils.isNotEmpty(conditions.getStart_time())) sqlVo.appendSqlAndAddParam("AND A.INPUT_TIMESTAMP >= ?", conditions.getStart_time());
        if (StringUtils.isNotEmpty(conditions.getEnd_time())) sqlVo.appendSqlAndAddParam("AND A.INPUT_TIMESTAMP <= ?", conditions.getEnd_time());
        sqlVo.appendSql("ORDER BY A.INPUT_TIMESTAMP");
        List<HalmSourceDataExportReportDataBean> dataBean_list = mySql_QueryRunner_QueryListBySql(sqlVo, HalmSourceDataExportReportDataBean.class);

        try {
            XSSFWorkbook workbook = new XSSFWorkbook();
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            getHSSFWorkbook(dataBean_list, workbook);
            workbook.write(baos);
            baos.flush();
            baos.close();
            response.reset();
            response.addHeader("Content-Disposition", "attachment;filename=" + new String("halm机源数据-导出".getBytes(), "ISO-8859-1") + new SimpleDateFormat("yyyyMMddHHmmss").format(Calendar.getInstance().getTime()) + ".xlsx");
            response.addHeader("Content-Length", String.valueOf(baos.size()));
            response.setContentType("application/vnd.ms-excel");
            response.getOutputStream().write(baos.toByteArray());
        }catch (IOException e) {
            e.printStackTrace();
            log.error(e.getMessage());
        }

    }




    /*** 生成Excel表格 **/
    private XSSFWorkbook getHSSFWorkbook(List<HalmSourceDataExportReportDataBean> dataBean_list, XSSFWorkbook wb){
        if(wb == null) wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("sheet_1");


        XSSFRow row_1 = sheet.createRow(0);
        row_1.setHeight((short)(12*20)); //设置行高  基数为20
        XSSFCell row_1_cell_1 = row_1.createCell(0);
        row_1_cell_1.setCellValue("系统唯一码");
        row_1_cell_1.setCellStyle(getTitle1Style(wb));

        XSSFCell row_1_cell_2 = row_1.createCell(1);
        row_1_cell_2.setCellValue("INPUT时间");
        row_1_cell_2.setCellStyle(getTitle1Style(wb));

        XSSFCell row_1_cell_3 = row_1.createCell(2);
        row_1_cell_3.setCellValue("设备ID");
        row_1_cell_3.setCellStyle(getTitle1Style(wb));

        XSSFCell row_1_cell_4 = row_1.createCell(3);
        row_1_cell_4.setCellValue("Bin");
        row_1_cell_4.setCellStyle(getTitle1Style(wb));

        XSSFCell row_1_cell_5 = row_1.createCell(4);
        row_1_cell_5.setCellValue("Eta");
        row_1_cell_5.setCellStyle(getTitle1Style(wb));

        XSSFCell row_1_cell_6 = row_1.createCell(5);
        row_1_cell_6.setCellValue("Uoc");
        row_1_cell_6.setCellStyle(getTitle1Style(wb));

        XSSFCell row_1_cell_7 = row_1.createCell(6);
        row_1_cell_7.setCellValue("Isc");
        row_1_cell_7.setCellStyle(getTitle1Style(wb));

        XSSFCell row_1_cell_8 = row_1.createCell(7);
        row_1_cell_8.setCellValue("FF");
        row_1_cell_8.setCellStyle(getTitle1Style(wb));

        XSSFCell row_1_cell_9 = row_1.createCell(8);
        row_1_cell_9.setCellValue("Rseriec891");
        row_1_cell_9.setCellStyle(getTitle1Style(wb));

        XSSFCell row_1_cell_10 = row_1.createCell(9);
        row_1_cell_10.setCellValue("Rshuntdfdr");
        row_1_cell_10.setCellStyle(getTitle1Style(wb));

        XSSFCell row_1_cell_11 = row_1.createCell(10);
        row_1_cell_11.setCellValue("Irev2");
        row_1_cell_11.setCellStyle(getTitle1Style(wb));

        XSSFCell row_1_cell_12 = row_1.createCell(11);
        row_1_cell_12.setCellValue("Irtmax");
        row_1_cell_12.setCellStyle(getTitle1Style(wb));

        XSSFCell row_1_cell_13 = row_1.createCell(12);
        row_1_cell_13.setCellValue("厂家、片源");
        row_1_cell_13.setCellStyle(getTitle1Style(wb));

        XSSFCell row_1_cell_14 = row_1.createCell(13);
        row_1_cell_14.setCellValue("基地");
        row_1_cell_14.setCellStyle(getTitle1Style(wb));

        XSSFCell row_1_cell_15 = row_1.createCell(14);
        row_1_cell_15.setCellValue("批次号");
        row_1_cell_15.setCellStyle(getTitle1Style(wb));

        XSSFCell row_1_cell_16 = row_1.createCell(15);
        row_1_cell_16.setCellValue("线别");
        row_1_cell_16.setCellStyle(getTitle1Style(wb));

        XSSFCell row_1_cell_17 = row_1.createCell(16);
        row_1_cell_17.setCellValue("Comment");
        row_1_cell_17.setCellStyle(getTitle1Style(wb));

        XSSFCell row_1_cell_18 = row_1.createCell(17);
        row_1_cell_18.setCellValue("单/多晶");
        row_1_cell_18.setCellStyle(getTitle1Style(wb));

        XSSFCell row_1_cell_19 = row_1.createCell(18);
        row_1_cell_19.setCellValue("投料日期");
        row_1_cell_19.setCellStyle(getTitle1Style(wb));

        XSSFCell row_1_cell_20 = row_1.createCell(19);
        row_1_cell_20.setCellValue("晶片规格");
        row_1_cell_20.setCellStyle(getTitle1Style(wb));

        for (int i=1; i<dataBean_list.size(); i++) {
            XSSFRow row = sheet.createRow(i);
            row.setHeight((short)(12*20)); //设置行高  基数为20
            XSSFCell cell_1 = row.createCell(0);
            cell_1.setCellValue(dataBean_list.get(i).getEvt_seq_id());

            XSSFCell cell_2 = row.createCell(1);
            cell_2.setCellValue(dataBean_list.get(i).getInput_timestamp());

            XSSFCell cell_3 = row.createCell(2);
            cell_3.setCellValue(dataBean_list.get(i).getTool_id_fk());

            XSSFCell cell_4 = row.createCell(3);
            cell_4.setCellValue(dataBean_list.get(i).getBin());

            XSSFCell cell_5 = row.createCell(4);
            cell_5.setCellValue(dataBean_list.get(i).getEta());

            XSSFCell cell_6 = row.createCell(5);
            cell_6.setCellValue(dataBean_list.get(i).getUoc());

            XSSFCell cell_7 = row.createCell(6);
            cell_7.setCellValue(dataBean_list.get(i).getIsc());

            XSSFCell cell_8 = row.createCell(7);
            cell_8.setCellValue(dataBean_list.get(i).getFf());

            XSSFCell cell_9 = row.createCell(8);
            cell_9.setCellValue(dataBean_list.get(i).getRseriec891());

            XSSFCell cell_10 = row.createCell(9);
            cell_10.setCellValue(dataBean_list.get(i).getRshuntdfdr());

            XSSFCell cell_11 = row.createCell(10);
            cell_11.setCellValue(dataBean_list.get(i).getIrev2());

            XSSFCell cell_12 = row.createCell(11);
            cell_12.setCellValue(dataBean_list.get(i).getIrtmax());

            XSSFCell cell_13 = row.createCell(12);
            cell_13.setCellValue(dataBean_list.get(i).getCus_id_fk());

            XSSFCell cell_14 = row.createCell(13);
            cell_14.setCellValue(dataBean_list.get(i).getBase_id());

            XSSFCell cell_15 = row.createCell(14);
            cell_15.setCellValue(dataBean_list.get(i).getLot_id());

            XSSFCell cell_16 = row.createCell(15);
            cell_16.setCellValue(dataBean_list.get(i).getLine_id_fk());

            XSSFCell cell_17 = row.createCell(16);
            cell_17.setCellValue(dataBean_list.get(i).getComment());

            XSSFCell cell_18 = row.createCell(17);
            cell_18.setCellValue(dataBean_list.get(i).getAttributes());

            XSSFCell cell_19 = row.createCell(18);
            cell_19.setCellValue(dataBean_list.get(i).getFeeding_date());

            XSSFCell cell_20 = row.createCell(19);
            cell_20.setCellValue(dataBean_list.get(i).getWafer_spec());
        }
        return wb;
    }


    private static XSSFCellStyle getTitle1Style(XSSFWorkbook workbook){
        // 设置字体
        XSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 13); //字体高度
        font.setFontName("黑体"); //字体
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度
        //创建单元格的 显示样式
        XSSFCellStyle style = workbook.createCellStyle();
        style.setFont(font);
        style.setWrapText(true);
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//创建一个居中格式
        style.setAlignment(CellStyle.ALIGN_CENTER); //水平方向上的对其方式
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);  //垂直方向上的对其方式
        return style;
    }

}
